<?php

/**
 * Description of UtenteDataManager
 *
 * @author rocco
 */
require_once 'Utente.php';
require_once '../bean/Scuola.php';

class UtenteDataManager
{

    private static $instance;

    public static function getInstance()
    {
        if (self::$instance == null)
        {
            $c = __CLASS__;
            self::$instance = new $c;
        }
        return self::$instance;
    }

    //TODO...aggiornare il server con questa versione
    public function checkLogin($connection, $username, $password, $scuola)
    {
        $result = -1;
        try
        {
            $query = "Select codice from Utente where username = ? and password = ? and attivo = 1 AND codiceScuola = ?";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("ssi", $username, $password, $scuola);
            $stmt->execute();

            $stmt->bind_result($codiceUtente);
            if ($stmt->fetch())
                $result = $codiceUtente;
        } catch (Exception $ex)
        {

            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }


    public function checkLoginStudente($connection, $username, $password, $scuola)
    {
        $result = -1;
        try
        {
            $query = "Select codice from Studente where username = ? and password = ? AND codiceScuola = ?";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("ssi", $username, $password, $scuola);
            $stmt->execute();

            $stmt->bind_result($codiceUtente);
            if ($stmt->fetch())
                $result = $codiceUtente;
        } catch (Exception $ex)
        {

            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }

    public function insertUtente($connection, $obj)
    {
        try
        {
            $query = "Insert into Utente (username, password, nome, cognome, email, attivo) VALUES(?, ?, ?, ?, ?, ?) ";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("sssssi", $obj->getUsername(), md5($obj->getPassword()), $obj->getNome(), $obj->getCognome(), $obj->getEmail(), $obj->getAttivo()))
                throw new Exception("Errore in bind_param query : " . $query);

            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        } catch (Exception $ex)
        {
            $header = "From: Admin<info@softwareeasy.it>\n";
            $oggetto = "errore in UtenteDataManager:insertUtente - utente : " . $_COOKIE["codiceUtente"];
            $messaggio = " Message : " . $ex->getMessage() . " trace " . $ex->getTraceAsString();
            mail("casaranese@gmail.com", $oggetto, $messaggio, $header);
        }
        if ($stmt != NULL)
            $stmt->close();
    }

//    public function getUtentiByData($connection, $from, $to)
//    {
//        $query = "Select codice, username, nome, cognome from Utente where UNIX_TIMESTAMP(dataCreazione) >= ? and UNIX_TIMESTAMP(dataCreazione) <= ? ";
//        $stmt = $connection->prepare($query);
//        $stmt->bind_param("ii", $from, $to);
//        $stmt->execute();
////        echo 'from : '.$from .' to : ' .$to;
//        return $stmt;
//    }

//    public function associaUtenteClasse($connection, $codiceUtente, $codiceClasse)
//    {
////        echo 'utente : '.$codiceUtente. ' classe ' . $codiceClasse;
//        $query = "Insert into UtenteClasse (codiceUtente, codiceClasse) VALUES(?, ?) ";
//        $stmt = $connection->prepare($query);
//        $stmt->bind_param("ii", $codiceUtente, $codiceClasse);
//        $stmt->execute();
//        $stmt->close();
//    }

//    public function getUtenteClasse($connection, $codiceUtente)
//    {
//        $query = "Select Classe.codice, Classe.classe, Classe.sezione,Indirizzo.descrizione  from Classe, UtenteClasse, Indirizzo where Classe.codice= UtenteClasse.codiceClasse and Classe.codiceIndirizzo=Indirizzo.codice and UtenteClasse.codiceUtente = ? ";
//        $stmt = $connection->prepare($query);
//        $stmt->bind_param("i", $codiceUtente);
//        $stmt->execute();
//
//        return $stmt;
//    }

//    public function eliminaUtenteClasse($connection, $codiceUtente, $codiceClasse)
//    {
//        $query = "delete from UtenteClasse where codiceUtente = ? and codiceClasse = ?";
//        $stmt = $connection->prepare($query);
//        $stmt->bind_param("ii", $codiceUtente, $codiceClasse);
//        $stmt->execute();
//        $stmt->close();
//    }

    public function insertStudente($connection, $nome, $cognome, $email, $codiceClasse, $username, $password)
    {
        $query = "Insert into Studente (nome, cognome, email, codiceClasse, password, codiceScuola) VALUES(?, ?, ?, ?, ?, ?) ";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("sssisi", $nome, $cognome, $email, $codiceClasse,  $password, $_COOKIE["codiceScuola"]);
        $stmt->execute();
        $stmt->close();
    }

    public function get($connection, $codiceUtente)
    {
        $query = sprintf("Select codice, username, nome, cognome, email, attivo, dataCreazione from Utente where codice = %d", $codiceUtente);
        $stmt = $connection->prepare($query);
        $stmt->execute();
        $stmt->bind_result($codice, $username, $nome, $cognome, $email, $attivo, $dataCreazione);
        $stmt->fetch();
        $utente = new Utente();
        $utente->setAttivo($attivo);
        $utente->setCodice($codice);
        $utente->setCogome(stripslashes($cognome));
        $utente->setEmail(stripslashes($email));
        $utente->setNome(stripslashes($nome));
        $utente->setUsername(stripslashes($username));
        $utente->setDataCreazione($dataCreazione);
        return $utente;
    }

    public function getDisciplinaByCodice($connection, $codiceUtente)
    {
        $query = sprintf("Select Discipline.nome as nome from Discipline, UtenteDisciplina where UtenteDisciplina.codiceDisciplina = Discipline.codice and UtenteDisciplina.codiceUtente = %d ", $codiceUtente);

        if ($stmt = $connection->prepare($query))
            $stmt->execute();

        $stmt->bind_result($nome);

        $lista = array();


        while ($stmt->fetch())
        {
            $lista[] = $nome;
        }
        $stmt->close();
        return $lista;
    }

    public function getCodiceDisciplinaByCodice($connection, $codiceUtente)
    {
        $query = sprintf("Select Discipline.codice as codice from Discipline, UtenteDisciplina where UtenteDisciplina.codiceDisciplina = Discipline.codice and UtenteDisciplina.codiceUtente = %d ", $codiceUtente);

        if ($stmt = $connection->prepare($query))
            $stmt->execute();

        $stmt->bind_result($codice);

        $lista = array();


        while ($stmt->fetch())
        {
            $lista[] = $codice;
        }
        $stmt->close();
        return $lista;
    }

    public function updateUtente($connection, $codice, $attivo, $username, $nome, $cognome, $email)
    {
        $query = "Update Utente set  attivo = ?, username=?, nome = ?, cognome = ?, email = ? where codice = ?";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("issssi", $attivo, $username, $nome, $cognome, $email, $codice);
        $stmt->execute();
        $stmt->close();
    }

    public function getUserPassword($connection, $codiceUtente)
    {
        $query = "Select password from Utente where Utente.codice = ? ";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("i", $codiceUtente);
        $stmt->execute();
        $stmt->bind_result($password);
        $stmt->fetch();
        return $password;
    }

    public function updateUtentePassword($connection, $codice, $newPassword)
    {
        $query = "Update Utente set password = ? where codice = ?";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("si", md5($newPassword), $codice);
        $stmt->execute();
        $stmt->close();
    }

    public function insertUtenteDisciplina($connection, $codiceUtente, $codiceDisciplina)
    {
        $query = "Insert into UtenteDisciplina (codiceUtente, codiceDisciplina) VALUES(?, ?) ";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("ii", $codiceUtente, $codiceDisciplina);
        $stmt->execute();
        $stmt->close();
    }

    public function existUtenteDisciplina($connection, $codiceUtente, $codiceDisciplina)
    {
        $query = sprintf("Select codiceUtente from UtenteDisciplina where codiceUtente = %d AND codiceDisciplina = %d", $codiceUtente, $codiceDisciplina);

        if ($stmt = $connection->prepare($query))
            $stmt->execute();

        $stmt->bind_result($nome);

        $result = FALSE;
        if ($stmt->fetch())
            $result = TRUE;
        $stmt->close();
        return $result;
    }

    public function eliminaUtenteDisciplina($connection, $codiceUtente, $codiceDisciplina)
    {
        $query = "delete from UtenteDisciplina where codiceUtente = ? and codiceDisciplina = ?";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("ii", $codiceUtente, $codiceDisciplina);
        $stmt->execute();
        $stmt->close();
    }

    public function usernamePresent($connection, $username)
    {
        $query = "Select codice from Utente where Utente.username = ? ";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("s", $username);
        $stmt->execute();
        $stmt->bind_result($codice);
        $result = false;
        if ($stmt->fetch())
            $result = true;
        $stmt->close();
        return $result;
    }

    /* TODO DA FINIRE */

    public function getUsers($connection, $username, $nome, $cognome)
    {
        $query = "Select Utente.codice,
            Utente.nome,
            Utente.cognome,
            Utente.username,
            Utente.dataCreazione,
            Utente.email,
            Utente.attivo
            from Utente
            where codice != 1 ";

        if (!$username == "")
        {
            $query1 = " AND UPPER(Utente.username) LIKE UPPER('%" . mysql_escape_string($username) . "%')";
            $query = $query . $query1;
        }
        if ($nome !== "")
        {
            $query2 = " AND UPPER(Utente.nome) LIKE UPPER('%" . mysql_escape_string($nome) . "%')";
            $query = $query . $query2;
        }
        if ($codiceArgomento !== "")
        {
            $query3 = " AND UPPER(Utente.cognome) LIKE UPPER('%" . mysql_escape_string($cognome) . "%')";
            $query = $query . $query3;
        }

        $query = $query . "  order by Utente.codice ";

//        echo 'query = ' .$query;

        if ($stmt = $connection->prepare($query))
            $stmt->execute();

        $stmt->bind_result($codiceUtente, $nome, $cognome, $username, $dataRegistrazione, $email, $attivo);

        $lista = array();

        while ($stmt->fetch())
        {
            $utente = new Utente();
            $utente->setAttivo($attivo);
            $utente->setCodice($codiceUtente);
            $utente->setNome(stripslashes($nome));
            $utente->setCogome(stripslashes($cognome));
            $utente->setEmail(stripslashes($email));
            $utente->setDataCreazione($dataRegistrazione);
            $utente->setUsername(stripslashes($username));

            $lista[] = $utente;
        }
        $stmt->close();
        return $lista;
    }

    public function eliminaUtente($connection, $codiceUtente)
    {
        $query = "delete from Utente where codice = ? ";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("i", $codiceUtente);
        $stmt->execute();
        $stmt->close();

        $query = "delete from UtenteDisciplina where codiceUtente = ? ";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("i", $codiceUtente);
        $stmt->execute();
        $stmt->close();

        $query = "delete from Test where codiceUtente = ? ";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("i", $codiceUtente);
        $stmt->execute();
        $stmt->close();
    }

    public function getScuole($connection)
    {
        $query = sprintf("Select codice, nome from Scuole");

        if ($stmt = $connection->prepare($query))
            $stmt->execute();

        $stmt->bind_result($codice, $nome);

        $lista = array();


        while ($stmt->fetch())
        {
            $scuola = new Scuola();
            $scuola->setCodice($codice);
            $scuola->setNome($nome);
            $lista[] = $scuola;
        }
        $stmt->close();
        return $lista;
    }

    public function updateStudente($connection, $codice, $nome, $cognome, $username, $password, $email)
    {
        $query = "Update Studente set username=?, nome = ?, cognome = ?, email = ?, password = ? where codice = ?";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("sssssi", $username, $nome, $cognome, $email, $password, $codice);
        $stmt->execute();
        $stmt->close();
    }

    public function forcePassword($connection, $password, $codice)
    {
        $query = "Update Studente set password = ? where codice = ?";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("si", $password, $codice);
        $stmt->execute();
        $stmt->close();
    }

    public function getLastCodiceStudente($connection)
    {
        $query = "Select max(codice) as res from Studente ";
        if($stmt = $connection->prepare($query))
            $stmt->execute();

        $result = -1;
        $stmt->bind_result($res);
        if($stmt->fetch())
            $result = $res;
        $stmt->close();

        return $result;
    }

    public function updateUsernameStudente($connection, $codice)
    {
        $query = "Update Studente set username = ? where codice = ?";
        $stmt = $connection->prepare($query);
        $stmt->bind_param("ss", $codice, $codice);
        $stmt->execute();
        $stmt->close();
    }

}
?>
